﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Db
{
    public class DbConnect
    {
        private static DbConnect instance = new DbConnect();
        private SqlConnection connection = null;

        private const string USERNAME = "dm72_1";
        private const string PASSWORD = "MaaGodt";
        private const string SERVER = "balder.ucn.dk";
        //private const string SERVER = @"RaWn7x64-PC\RAVNSQL";
        private const string DATABASE = "dm72_1";
        private const int TIMEOUT = 20;

        private const bool DEBUG = true;

        private DbConnect()
        {
            if (connection == null)
            {
                try
                {
                    this.connection = new SqlConnection("user id=" + USERNAME + ";" +
                                            "password=" + PASSWORD + ";" +
                                            "server=" + SERVER + ";" +
                                            "database=" + DATABASE + "; " +
                                            "MultipleActiveResultSets=True;" +
                                            "connection timeout=" + TIMEOUT);

                    this.connection.Open();

                    if (DEBUG)
                    {
                        this.ShowConnectionStatus(this.connection);
                    }
                }
                catch (Exception e)
                {
                    this.connection = null;
                    Console.WriteLine(e.ToString());
                }
            }
        }

        public static DbConnect Instance
        {
            get { return instance; }
        }

        private void ShowConnectionStatus(SqlConnection cn)
        {
            // Show various stats about current connection object.
            Console.WriteLine("***** Info about your connection *****");
            Console.WriteLine("Database location: {0}", cn.DataSource); 
            Console.WriteLine("Database name: {0}", cn.Database);
            Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout);
            Console.WriteLine("Connection state: {0}\n", cn.State.ToString());
        }


        // READ
        public SqlDataReader Query(Statement s)
        {
            SqlCommand cmd = this.BuildCommand(s);
            return this.ExecuteRead(cmd);
        }

        public SqlDataReader Query(string s)
        {
            Statement stmt = new Statement(s);
            return this.Query(stmt);
        }

        // INSERT
        public int QueryInsert(Statement s)
        {
            SqlCommand cmd = this.BuildCommand(s);
            return this.ExecuteInsert(cmd);
        }

        public int QueryInsert(string s)
        {
            Statement stmt = new Statement(s);
            return this.QueryInsert(stmt);
        }

        // UPDATE
        public int QueryUpdate(Statement s)
        {
            SqlCommand cmd = this.BuildCommand(s);
            return this.ExecuteUpdate(cmd);
        }

        public int QueryUpdate(string s)
        {
            Statement stmt = new Statement(s);
            return this.QueryUpdate(stmt);
        }

        // DELETE
        public int QueryDelete(Statement s)
        {
            SqlCommand cmd = this.BuildCommand(s);
            return this.ExecuteUpdate(cmd);
        }

        public int QueryDelete(string s)
        {
            Statement stmt = new Statement(s);
            return this.QueryUpdate(stmt);
        }

        public bool CommitTransaction(Transaction trans)
        {
            bool result = false;

            SqlCommand command = connection.CreateCommand();

            SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");

            command.Connection = connection;
            command.Transaction = transaction;

            try
            {
                foreach (Statement s in trans.Statements)
                {
                    command.CommandText = s.Stmt;
                    command.Parameters.Clear();
                    if (s.Parameters.Count > 0)
                    {
                        foreach (SqlParameter par in s.Parameters)
                        {
                            command.Parameters.Add(par);
                        }
                    }

                    command.ExecuteNonQuery();
                }

                transaction.Commit();
                result = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in transaction: " + ex);

                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    Console.WriteLine("Could not roll back! (" + ex2 + ")");
                }
            }

            //connection.
            transaction.Dispose();

            return result;
        }

        private SqlCommand BuildCommand(Statement stmt)
        {
            SqlCommand command = null;

            if (connection != null)
            {
                command = connection.CreateCommand();

                if (command != null && stmt.Stmt.Length > 0)
                {
                    command.CommandText = stmt.Stmt;

                    if (stmt.Parameters.Count > 0)
                    {
                        foreach (SqlParameter par in stmt.Parameters)
                        {
                            command.Parameters.Add(par);
                        }
                    }
                }
            }

            return command;
        }

        private SqlDataReader ExecuteRead(SqlCommand command)
        {
            SqlDataReader result = null;
            if (command != null)
            {
                try
                {
                    PrintStatement(command);
                    result = command.ExecuteReader();
                }
                catch (SqlException e)
                {
                    Console.WriteLine("SQL error: " + e.ToString() + " (" + command.CommandText + ")");
                }

            }
            return result;
        }

        private int ExecuteUpdate(SqlCommand command)
        {
            int result = 0;
            if (command != null)
            {
                try
                {
                    this.PrintStatement(command);
                    result = command.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    Console.WriteLine("SQL error: " + e.ToString() + " (" + command.CommandText + ")");
                }
            }
            return result;
        }

        private int ExecuteInsert(SqlCommand command)
        {
            decimal id = 0;
            int result = 0;
            if (command != null)
            {
                try
                {
                    this.PrintStatement(command);
                    result = command.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    Console.WriteLine("SQL error: " + e.ToString() + " (" + command.CommandText + ")");
                }

                if (result > 0)
                {
                    try
                    {
                        SqlCommand id_cmd = new SqlCommand("SELECT @@IDENTITY;", connection);
                        id = (decimal)id_cmd.ExecuteScalar();
                        //SqlCommand id_cmd = new SqlCommand("SELECT scope_identity();", connection);
                        //id = (Int32)id_cmd.ExecuteScalar();

                        if (id == 0)
                        {
                            // sqlserver earlier version
                            //id_cmd = new SqlCommand("SELECT @@IDENTITY", connection);
                            //id = (Int32)id_cmd.ExecuteScalar();
                        }
                    }
                    catch (SqlException e)
                    {
                        Console.WriteLine("SQL error: " + e.ToString() + " (" + command.CommandText + ")");
                    }
                }
            }

            return Convert.ToInt32(id);
        }

        private void PrintStatement(SqlCommand command)
        {
            if (DEBUG)
            {
                Console.WriteLine("Execute SQL: " + command.CommandText);
            }
        }

        public float GetMax(string table, string field)
        {
            float data = -1;
            if (table.Length > 0 && field.Length > 0)
            {
                try
                {
                    Statement stmt = new Statement("SELECT MAX(" + field + ") AS max FROM " + table);

                    SqlDataReader q = this.Query(stmt);
                    if (q != null && q.Read())
                    {
                        data = q.GetFloat(q.GetOrdinal("max"));
                    }
                }
                catch (Exception e)
                {
                    if (DEBUG)
                    {
                        Console.WriteLine(e);
                    }
                }
            }
            
            return data;
        }
    }
}
